﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbCodeGenerator
{
    public class SqlServerDbHandle : IDbHandle
    {
        public string ServerAddress { get; set; }
        public string UserID { get; set; }
        public string Password { get; set; }
        public uint Port { get; set; }
        public IDbConnection getDbConnection()
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = ServerAddress;
            sqlBuilder.UserID = UserID;
            sqlBuilder.Password = Password;
            return new SqlConnection(sqlBuilder.ConnectionString);
        }
        public IDbConnection getDbConnection(string dbname)
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = ServerAddress;
            sqlBuilder.UserID = UserID;
            sqlBuilder.Password = Password;
            sqlBuilder.InitialCatalog = dbname;
            return new SqlConnection(sqlBuilder.ConnectionString);
        }
        public DataTable getDatabases()
        {

            //sqlBuilder.Port = Port;
            DataTable dt = null;
            using (SqlConnection sqlconn = (SqlConnection)getDbConnection())
            {
                sqlconn.Open();
                dt = sqlconn.GetSchema("Databases");
                sqlconn.Close();
            }
            return dt;
        }
        public DataTable getTables(string dbname)
        {
            DataTable dt = null;
            using (SqlConnection sqlconn = (SqlConnection)getDbConnection(dbname))
            {
                sqlconn.Open();
                dt = sqlconn.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });
                sqlconn.Close();
            }
            return dt;
        }
        public DataTable getColumns(string dbname, string tabname)
        {
            DataTable dt = null;
            using (SqlConnection sqlconn = (SqlConnection)getDbConnection(dbname))
            {
                DataTable stab = null;
                try
                {
                    sqlconn.Open();

                    dt = sqlconn.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, tabname });
                    dt.Columns.Add("COLUMN_KEY", typeof(string));
                    dt.Columns.Add("EXTRA", typeof(string));
                    dt.Columns.Add("COLUMN_TYPE", typeof(string));

                    SqlCommand cmd = new SqlCommand("select top 1 * from " + tabname, sqlconn);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                    stab = reader.GetSchemaTable();
                }
                catch
                {
                    return null;
                }
                finally
                {
                    sqlconn.Close();
                }

                foreach (DataRow row in dt.Rows)
                {
                    string colname = row["column_name"].ToString();
                    DataRow[] rows = stab.Select("ColumnName='" + colname + "'");
                    if (rows.Length > 0)
                    {
                        bool IsKey = (bool)rows[0]["IsKey"];
                        bool IsAutoIncrement = (bool)rows[0]["IsAutoIncrement"];
                        string defval= row["COLUMN_DEFAULT"].ToString();
                        row.BeginEdit();
                        if (IsKey)
                        {
                            row["COLUMN_KEY"] = "PRI";
                        }
                        if (IsAutoIncrement)
                        {
                            row["EXTRA"] = "auto_increment";
                        }
                        if (!string.IsNullOrEmpty(defval))
                        {
                            row["COLUMN_DEFAULT"] = defval.Replace("((", "").Replace("))", "");
                        }
                        row.EndEdit();
                    }
                }
                dt.AcceptChanges();
            }
            return dt;
        }

    }
}
